--DROP VIEW GSLMS.V_ADU_ASSIGN;

/* Formatted on 2013/12/12 14:44:51 (QP5 v5.227.12220.39724) */
CREATE OR REPLACE FORCE VIEW GSLMS.V_ADU_ASSIGN
(
   ID,
   REGINFOID,
   CUSTNAME,
   DOCTYPE,
   DOCTYPENAME,
   CUSTNO,
   APPLYNO,
   APPDATE,
   LOANTYPE,
   SALESMANID,
   SALESMAN,
   DEPID,
   DEPNAME,
   BUSSTYPE,
   PRODUCTID,
   PRODUCTNAME,
   MEDIATORID,
   MEDIATOR,
   MEDIATORACCOUNT,
   MEDIATORTEL,
   SOURCE,
   ISSPECIAL,
   SPECID,
   CONTACT,
   CONTRACTTEL,
   CONTRACTTEL2,
   AMOUNT,
   LOANPHASE,
   LOANPHAESTYPE,
   RATE,
   MANAGEFEERATE,
   GENERALRATE,
   LOANFEE,
   STATUS,
   CANCELBY,
   CANCELDATE,
   CANCELREASON,
   BACKSTATUS,
   BACKDATE,
   BACKBY,
   BACKREASON,
   FLOWSTATUS,
   SYSTEMID,
   SITENO,
   SITENAME,
   SITEID,
   SPAREFIELD1,
   SPAREFIELD2,
   SPAREFIELD3,
   SUBMITBY,
   SUBMITDATE,
   DEALINGSTATUS,
   ASSIGNBY,
   ASSIGNTIME,
   FLOWUSER
)
AS
   SELECT pi."ID",
          pi."REGINFOID",
          pi."CUSTNAME",
          pi."DOCTYPE",
          pi."DOCTYPENAME",
          pi."CUSTNO",
          pi."APPLYNO",
          pi."APPDATE",
          pi."LOANTYPE",
          pi."SALESMANID",
          pi."SALESMAN",
          pi."DEPID",
          pi."DEPNAME",
          pi."BUSSTYPE",
          pi."PRODUCTID",
          pi."PRODUCTNAME",
          pi."MEDIATORID",
          pi."MEDIATOR",
          pi."MEDIATORACCOUNT",
          pi."MEDIATORTEL",
          pi."SOURCE",
          pi."ISSPECIAL",
          pi."SPECID",
          pi."CONTACT",
          pi."CONTRACTTEL",
          pi."CONTRACTTEL2",
          pi."AMOUNT",
          pi."LOANPHASE",
          pi."LOANPHAESTYPE",
          pi."RATE",
          pi."MANAGEFEERATE",
          pi."GENERALRATE",
          pi."LOANFEE",
          pi."STATUS",
          pi."CANCELBY",
          pi."CANCELDATE",
          pi."CANCELREASON",
          pi."BACKSTATUS",
          pi."BACKDATE",
          pi."BACKBY",
          pi."BACKREASON",
          pi."FLOWSTATUS",
          pi."SYSTEMID",
          pi."SITENO",
          pi."SITENAME",
          pi."SITEID",
          pi."SPAREFIELD1",
          pi."SPAREFIELD2",
          pi."SPAREFIELD3",
          pi."SUBMITBY",
          pi."SUBMITDATE",
          (CASE WHEN AA.ID > 0 THEN 1 ELSE 0 END) dealingstatus,
          AA.ASSIGNBY,
          AA.ASSIGNTIME,
          AA.FLOWUSER
     FROM tb_app_proinfo pi
          JOIN TB_ADU_ASSIGN aa ON PI.ID = AA.PROID(+) AND pi.status >= 21;


--DROP VIEW GSLMS.V_ADU_CONARR;

/* Formatted on 2013/12/12 14:44:51 (QP5 v5.227.12220.39724) */
CREATE OR REPLACE FORCE VIEW GSLMS.V_ADU_CONARR
(
   ID,
   PROID,
   ARRDATE,
   ARRMEMBERS,
   ARRMEMBERSID,
   HEADMAN,
   HEADMANID,
   ARRBY,
   ARRBYID,
   CREATEDATE,
   REMARK,
   STATUS,
   CUSTNAME,
   CUSTNO,
   SYSTEMID,
   AMOUNT,
   BUSSTYPE,
   APPLYNO,
   APPDATE,
   SALESMANID,
   SALESMAN,
   PRODUCTID,
   PRODUCTNAME,
   APPLYDATE,
   DIRECTORAUDITTIME,
   RISKAUDITBY,
   GENERALRATE
)
AS
   SELECT ac."ID",
          ac."PROID",
          ac."ARRDATE",
          ac."ARRMEMBERS",
          ac."ARRMEMBERSID",
          ac."HEADMAN",
          ac."HEADMANID",
          ac."ARRBY",
          ac."ARRBYID",
          ac."CREATEDATE",
          ac."REMARK",
          ac."STATUS",
          ap.custname,
          ap.custno,
          ap.systemid,
          ap.amount,
          ap.busstype,
          ap.applyno,
          ap.appdate,
          AP.SALESMANID,
          ap.salesman,
          ap.productid,
          ap.productname,
          ap.APPDATE applydate,
          (SELECT audittime
             FROM TB_App_DirectorAudit
            WHERE id = (SELECT MAX (id)
                          FROM TB_App_DirectorAudit
                         WHERE proid = ac.proid))
             directoraudittime,
          (SELECT auditby
             FROM TB_ADU_RISKCONTROL
            WHERE id = (SELECT MAX (id)
                          FROM TB_ADU_RISKCONTROL
                         WHERE proid = ac.proid))
             riskauditby,
          ap.GENERALRATE
     FROM tb_adu_conarr ac LEFT JOIN tb_app_proinfo ap ON ac.proid = ap.id;


--DROP VIEW GSLMS.V_ADU_RISKCONTROL;

/* Formatted on 2013/12/12 14:44:52 (QP5 v5.227.12220.39724) */
CREATE OR REPLACE FORCE VIEW GSLMS.V_ADU_RISKCONTROL
(
   ID,
   REGINFOID,
   CUSTNAME,
   DOCTYPE,
   DOCTYPENAME,
   CUSTNO,
   APPLYNO,
   APPDATE,
   LOANTYPE,
   SALESMANID,
   SALESMAN,
   DEPID,
   DEPNAME,
   BUSSTYPE,
   PRODUCTID,
   PRODUCTNAME,
   MEDIATORID,
   MEDIATOR,
   MEDIATORACCOUNT,
   MEDIATORTEL,
   SOURCE,
   ISSPECIAL,
   SPECID,
   CONTACT,
   CONTRACTTEL,
   CONTRACTTEL2,
   AMOUNT,
   LOANPHASE,
   LOANPHAESTYPE,
   RATE,
   MANAGEFEERATE,
   GENERALRATE,
   LOANFEE,
   STATUS,
   CANCELBY,
   CANCELDATE,
   CANCELREASON,
   BACKSTATUS,
   BACKDATE,
   BACKBY,
   BACKREASON,
   FLOWSTATUS,
   SYSTEMID,
   SITENO,
   SITENAME,
   SITEID,
   SPAREFIELD1,
   SPAREFIELD2,
   SPAREFIELD3,
   SUBMITBY,
   SUBMITDATE,
   DEALINGSTATUS,
   ASSIGNBY,
   ASSIGNTIME,
   FLOWUSER,
   RISKAUDITID,
   RISKAUDITBY,
   RISKAUDITTIME,
   RISKAUDITSTATUS
)
AS
   SELECT va."ID",
          va."REGINFOID",
          va."CUSTNAME",
          va."DOCTYPE",
          va."DOCTYPENAME",
          va."CUSTNO",
          va."APPLYNO",
          va."APPDATE",
          va."LOANTYPE",
          va."SALESMANID",
          va."SALESMAN",
          va."DEPID",
          va."DEPNAME",
          va."BUSSTYPE",
          va."PRODUCTID",
          va."PRODUCTNAME",
          va."MEDIATORID",
          va."MEDIATOR",
          va."MEDIATORACCOUNT",
          va."MEDIATORTEL",
          va."SOURCE",
          va."ISSPECIAL",
          va."SPECID",
          va."CONTACT",
          va."CONTRACTTEL",
          va."CONTRACTTEL2",
          va."AMOUNT",
          va."LOANPHASE",
          va."LOANPHAESTYPE",
          va."RATE",
          va."MANAGEFEERATE",
          va."GENERALRATE",
          va."LOANFEE",
          va."STATUS",
          va."CANCELBY",
          va."CANCELDATE",
          va."CANCELREASON",
          va."BACKSTATUS",
          va."BACKDATE",
          va."BACKBY",
          va."BACKREASON",
          va."FLOWSTATUS",
          va."SYSTEMID",
          va."SITENO",
          va."SITENAME",
          va."SITEID",
          va."SPAREFIELD1",
          va."SPAREFIELD2",
          va."SPAREFIELD3",
          va."SUBMITBY",
          va."SUBMITDATE",
          va."DEALINGSTATUS",
          va."ASSIGNBY",
          va."ASSIGNTIME",
          va."FLOWUSER",
          ar.id riskauditid,
          ar.AUDITBY riskauditby,
          ar.AUDITTIME riskaudittime,
          NVL (ar.STATUS, 0) riskauditstatus
     FROM V_ADU_ASSIGN va
          JOIN TB_ADU_RISKCONTROL ar
             ON va.id = AR.PROID(+) AND va.status >= 31;


--DROP VIEW GSLMS.V_APP_AUDIT;

/* Formatted on 2013/12/12 14:44:52 (QP5 v5.227.12220.39724) */
CREATE OR REPLACE FORCE VIEW GSLMS.V_APP_AUDIT
(
   ID,
   PROID,
   AUDITTYPE,
   AUDITBY,
   AUDITTIME,
   AUDITRESULT,
   AUDITCONTENT,
   APPLYNO,
   CUSTNAME,
   CUSTNO
)
AS
   SELECT DISTINCT aa."ID",
                   aa."PROID",
                   aa."AUDITTYPE",
                   aa."AUDITBY",
                   aa."AUDITTIME",
                   aa."AUDITRESULT",
                   aa."AUDITCONTENT",
                   ap.applyno,
                   ap.custname,
                   ap.custno
     FROM TB_APP_AUDIT aa LEFT JOIN tb_app_proinfo ap ON aa.proid = ap.id;


--DROP VIEW GSLMS.V_APP_DIRECTORAUDIT;

/* Formatted on 2013/12/12 14:44:52 (QP5 v5.227.12220.39724) */
CREATE OR REPLACE FORCE VIEW GSLMS.V_APP_DIRECTORAUDIT
(
   ID,
   PROID,
   AUDITBY,
   AUDITTIME,
   AUDITRESULT,
   AUDITCONTENT,
   TOUSER,
   APPLYNO,
   CUSTNAME,
   CUSTNO,
   TOUSERNAME,
   SYSTEMID
)
AS
   SELECT DISTINCT aa."ID",
                   aa."PROID",
                   aa."AUDITBY",
                   aa."AUDITTIME",
                   aa."AUDITRESULT",
                   aa."AUDITCONTENT",
                   aa."TOUSER",
                   ap.applyno,
                   ap.custname,
                   ap.custno,
                   (SELECT empname
                      FROM tb_sys_emp
                     WHERE username = aa.touser)
                      tousername,
                   ap.systemid
     FROM TB_APP_DirectorAUDIT aa
          LEFT JOIN tb_app_proinfo ap ON aa.proid = ap.id;


--DROP VIEW GSLMS.V_APP_REFREGINFO;

/* Formatted on 2013/12/12 14:44:52 (QP5 v5.227.12220.39724) */
CREATE OR REPLACE FORCE VIEW GSLMS.V_APP_REFREGINFO
(
   ID,
   REGINFOID,
   CUSTNAME,
   REFBY,
   REFBYNAME,
   APPDATE,
   OUTDEPID,
   OUTDEPNAME,
   OUTTIME,
   INDEPID,
   INDEPNAME,
   INTIME,
   SALESMANID,
   SALESMAN,
   DETAIL,
   STATUS,
   AUDITBY,
   AUDITTIME,
   BACKSTATUS,
   SPAREFIELD1,
   SPAREFIELD2,
   SPAREFIELD3,
   CUSTNO
)
AS
   SELECT DISTINCT r."ID",
                   r."REGINFOID",
                   r."CUSTNAME",
                   r."REFBY",
                   r."REFBYNAME",
                   r."APPDATE",
                   r."OUTDEPID",
                   r."OUTDEPNAME",
                   r."OUTTIME",
                   r."INDEPID",
                   r."INDEPNAME",
                   r."INTIME",
                   r."SALESMANID",
                   r."SALESMAN",
                   r."DETAIL",
                   r."STATUS",
                   r."AUDITBY",
                   r."AUDITTIME",
                   r."BACKSTATUS",
                   r."SPAREFIELD1",
                   r."SPAREFIELD2",
                   r."SPAREFIELD3",
                   ri.custno
     FROM TB_APP_REFREGINFO r
          LEFT JOIN tb_app_reginfo ri ON r.REGINFOID = ri.id;


--DROP VIEW GSLMS.V_APP_SPECIALINFO_ADU;

/* Formatted on 2013/12/12 14:44:52 (QP5 v5.227.12220.39724) */
CREATE OR REPLACE FORCE VIEW GSLMS.V_APP_SPECIALINFO_ADU
(
   ID,
   LOANID,
   PROID,
   CONID,
   PID,
   SPECIALTYPE,
   SUBMITDEPART,
   SUBMITUSER,
   AUDITTIME,
   AUDITSTATUS,
   REMARK,
   TODEPART,
   TOUSER,
   REGINFOID,
   CUSTNAME,
   SALESMANID,
   SALESMAN,
   PRODUCTID,
   PRODUCTNAME,
   CREATEDATE
)
AS
   SELECT DISTINCT s."ID",
                   s."LOANID",
                   s."PROID",
                   s."CONID",
                   s."PID",
                   s."SPECIALTYPE",
                   s."SUBMITDEPART",
                   s."SUBMITUSER",
                   s."AUDITTIME",
                   s."AUDITSTATUS",
                   s."REMARK",
                   s."TODEPART",
                   s."TOUSER",
                   asp.reginfoid,
                   asp.custname,
                   asp.SALESMANID,
                   asp.SALESMAN,
                   asp.PRODUCTID,
                   asp.PRODUCTNAME,
                   asp.CREATEDATE
     FROM TB_ADU_SPECAIL s
          LEFT JOIN tb_app_specialinfo asp
             ON s.SPECIALTYPE = '5' AND s.pid = asp.id;


--DROP VIEW GSLMS.V_PRODUCT_METINFOSETDETAIL;

/* Formatted on 2013/12/12 14:44:52 (QP5 v5.227.12220.39724) */
CREATE OR REPLACE FORCE VIEW GSLMS.V_PRODUCT_METINFOSETDETAIL
(
   INFONAME,
   METINFOSETDETAILID,
   SORTNO,
   PRODUCTID
)
AS
   SELECT d.INFONAME,
          d.id metinfosetdetailid,
          d.sortno,
          p.id productid
     FROM tb_sys_metinfosetmain m,
          tb_sys_metinfosetdetail d,
          tb_sys_product p
    WHERE m.id = d.METIINFOID AND m.id = p.METINFOSETID;
